IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CreateExternalPersonFromInfoEd]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CreateExternalPersonFromInfoEd]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



/***************************************************************************
* Name:			CreateExternalPersonFromInfoEd
* Purpose:		Maps the FacultyID from InfoEd to the AdmPersonId from Insight 
*				based first on NTLogin/username matching, then 
*				based on FacultyMap mapping
*
***************************************************************************/
CREATE PROCEDURE dbo.CreateExternalPersonFromInfoEd (
	@firstName			VARCHAR(70),
	@lastName			VARCHAR(70),
	@middleName			VARCHAR(25),
	@facultyId			VARCHAR(20),
	@email				VARCHAR(80)
)
AS
BEGIN

	SET NOCOUNT ON

	DECLARE @maxAdmPersonId			INT
	DECLARE @maxAdmExternalPersonId	INT
	DECLARE @errorCode				INT
	DECLARE @externalPersonType		INT

	SET @errorCode  = 0
	SET @externalPersonType = 2 -- 2 is External

	
	-- Setup the IDs to be inserted by selecting the max
	-- Id of the column, and if null, start at 1.
	SET @maxAdmPersonId = COALESCE (((SELECT MAX(Id) from AdmPerson)+1), 1)

	-- AdmExternalPerson
	SET @maxAdmExternalPersonId = COALESCE (((SELECT MAX(Id) from AdmExternalPerson)+1), 1)

	-- Named Transaction.
	BEGIN TRANSACTION CreateAdmPersonFromInfoEd

		SET IDENTITY_INSERT AdmPerson ON
		-- Insert the AdmPerson
		INSERT INTO AdmPerson	 (	Id, 
								FirstName, 
								LastName,
								MiddleName,
								AdmPersonTypeId,
								InsertedDate,
								FeedName  ) 
		VALUES
							 (	@maxAdmPersonId, 
								@firstName,	
								@lastName,
								@middleName,
								@externalPersonType,
								GETDATE(),
								'InfoEdExternalPeople' )
								
		SET IDENTITY_INSERT AdmPerson OFF
	
		SET @errorCode = @@ERROR
	
	    IF @errorCode <> 0 BEGIN
            ROLLBACK TRANSACTION CreateAdmPersonFromInfoEd
            RAISERROR('Unable to create AdmPerson record for AdmPerson %s', 16, 1, @lastName)
            RETURN @errorCode
        END


		-- Insert the AdmExternal Person record.
		SET IDENTITY_INSERT AdmExternalPerson ON
		
		INSERT INTO AdmExternalPerson ( Id, 
										AdmPersonId ) 
				VALUES	
									  ( @maxAdmExternalPersonId,
										@maxAdmPersonId )
		
		SET IDENTITY_INSERT AdmExternalPerson OFF
		
		SET @errorCode = @@ERROR
	
	    IF @errorCode <> 0 BEGIN
            ROLLBACK TRANSACTION CreateAdmPersonFromInfoEd
            RAISERROR('Unable to create AdmPerson record for AdmPerson %s', 16, 1, @lastName)
            RETURN @errorCode
        END


		INSERT INTO AdmPerson_InfoEdFaculty_Map ( AdmPersonId, 
						Unique_ID) 
			Values	( @maxAdmPersonId, 
					  @facultyId )

		IF @email <> NULL BEGIN
			INSERT INTO AdmEmailAddress (	EmailAddress, 
											AdmPersonId,
											AdmEmailAddressTypeId)
				VALUES
						(	@email,
							@maxAdmPersonId,
							1 )
		END

		
		-- COMMIT THE CHANGES.
		COMMIT TRANSACTION CreateAdmPersonFromInfoEd
		
	SET NOCOUNT OFF

	END



GO
